import 'dart:async';
import 'package:cloudapp/esdk/model/server_info.dart';
import 'package:path/path.dart';
import 'package:sqflite/sqflite.dart';

import 'model/local_album.dart';
import 'model/session_info.dart';
import 'model/sync_file.dart';

const ctLocalAlbum = '''
create table local_albums(
  id integer primary key,
  albumId text,
  serverId integer,
  sessionId integer
)''';
const ctSyncFile = '''
create table sync_files (
  id integer primary key,
  albumId text,
  fileName text,
  fileHash text,
  sysId text,
  fileId integer,
  sessionId integer
)''';
const ctSessionInfo = '''
create table session_info (
  id integer primary key,
  userId integer,
  serverId integer,
  lastUsed integer,
  loginResponse text
)''';
const ctServerInfo = '''
create table server_info(
  id integer primary key,
  inIpAddr text,
  outIpAddr text,
  serverHash text,
  port int
)''';

class DbStorage {
  Database db;
  DbStorage();

  static DbStorage _i;
  factory DbStorage.i() {
    if(_i == null) {
      _i = DbStorage();
    }

    return _i;
  }

  Future<void> initDb() async {
    try {
      db = await openDatabase(
          join(await getDatabasesPath(), 'esync.db'),
          version: 1,
          onCreate: (db, version) async {
            await db.execute(ctLocalAlbum);
            await db.execute(ctSyncFile);
            await db.execute(ctServerInfo);
            return await db.execute(ctSessionInfo);
          }
      );
      print('open database succeed.');
    } catch(err, stack) {
      print('init db catched exception');
      print(err);
      print(stack);
    }
  }

  // 相册操作
  Future<List<LocalAlbum>> findLocalAlbum(int sessionId, String localAlbumId) async {
    var maps = await db.query(
      'local_albums',
      where: 'albumId=? and sessionId=?',
      whereArgs: [localAlbumId, sessionId],
    );
    return List.generate(maps.length, (index) {
      return LocalAlbum(
        id: maps[index]['id'],
        albumId: maps[index]['albumId'],
        serverId: maps[index]['serverId'],
        sessionId: sessionId,
      );
    });
  }

  Future<List<LocalAlbum>> findAllLocalAlbum(int sessionId) async {
    var maps = await db.query(
      'local_albums',
      where: "sessionId=?",
      whereArgs: [sessionId],
    );
    return List.generate(maps.length, (index) {
      return LocalAlbum(
        id: maps[index]['id'],
        albumId: maps[index]['albumId'],
        serverId: maps[index]['serverId'],
        sessionId: maps[index]['sessionId'],
      );
    });
  }

  Future<void> removeLocalSyncAlbum(String albumId) async {
    return await db.delete(
      'local_albums',
      where: 'albumId=?',
      whereArgs: [albumId],
    );
  }

  Future<int> addLocalSyncAlbum(LocalAlbum item) async {
    var sql = 'insert into local_albums(albumId, serverId, sessionId) values(?, ?, ?)';
    return await db.rawInsert(sql, [item.albumId, item.serverId, item.sessionId]);
  }

  /// 同步文件操作
  Future<List<SyncFile>> getAllSyncFile(int sessionId) async {
    var maps = await db.query(
      'sync_files',
      where: "sessionId=?",
      whereArgs: [sessionId],
    );
    return List.generate(maps.length, (index) {
      return SyncFile(
        id: maps[index]['id'],
        albumId: maps[index]['albumId'],
        fileHash: maps[index]['fileHash'],
        sysId: maps[index]['sysId'],
        fileId: maps[index]['fileId'],
        sessionId: sessionId,
      );
    });
  }

  Future<int> addSyncFile(SyncFile f) async {
    var sql = 'insert into sync_files(albumId, fileHash, sysId, fileId, sessionId) values(?, ?, ?, ?, ?)';
    return await db.rawInsert(sql, [f.albumId, f.fileHash, f.sysId, f.fileId, f.sessionId]);
  }

  /// 会话操作 加载所有会话
  Future<List<SessionInfo>> loadAllSessions() async {
    var maps = await db.query(
      'session_info',
    );

    return List.generate(maps.length, (i) {
      return SessionInfo(
        id: maps[i]['id'],
        userId: maps[i]['userId'],
        serverId: maps[i]['serverId'],
        lastUsed: maps[i]['lastUsed'],
        loginResponse: maps[i]['loginResponse'],
      );
    });
  }

  /// 查询所有服务器
  Future<List<ServerInfo>> loadAllServers() async {
    var maps = await db.query(
      'server_info',
    );

    return List.generate(maps.length, (i) {
      return ServerInfo(
        id: maps[i]['id'],
        inIpAddr: maps[i]['inIpAddr'],
        outIpAddr: maps[i]['outIpAddr'],
        serverHash: maps[i]['serverHash'],
        port: maps[i]['port'],
      );
    });
  }

  /// 新增服务器
  Future<int> addServer(ServerInfo f) async {
    var sql = 'insert into server_info(inIpAddr, outIpAddr, serverHash, port) values(?, ?, ?, ?)';
    return await db.rawInsert(sql, [f.inIpAddr, f.outIpAddr, f.serverHash, f.port]);
  }

  /// 修改服务器IP地址
  Future<int> modServerAddr(int sId, String addr) async {
    var sql = 'update server_info set inIpAddr=? where id=?';
    return await db.rawUpdate(sql, [addr, sId]);
  }

  /// 查询某个会话的服务器数据
  Future<ServerInfo> getServerInfo(int serverId) async {
    var maps = await db.query(
      'server_info',
      where: "id=?",
      whereArgs: [serverId],
    );

    var rs = List.generate(maps.length, (i) {
      return ServerInfo(
        id: maps[i]['id'],
        inIpAddr: maps[i]['inIpAddr'],
        outIpAddr: maps[i]['outIpAddr'],
        serverHash: maps[i]['serverHash'],
        port: maps[i]['port'],
      );
    });

    if(rs.isEmpty) {
      return null;
    }
    return rs[0];
  }

  /// 新增会话记录
  Future<int> addSessionInfo(SessionInfo f) async {
    var sql = 'insert into session_info(userId, serverId, lastUsed, loginResponse) values(?, ?, ?, ?)';
    return await db.rawInsert(sql, [f.userId, f.serverId, f.lastUsed, f.loginResponse]);
  }

  // 修改会话记录 只能修改 lastUsed，response
  Future<int> saveSessionInfo(int sId, SessionInfo f) async {
    var sql = 'update session_info set lastUsed=?, loginResponse=? where id=?';
    return await db.rawUpdate(sql, [f.lastUsed, f.loginResponse, sId]);
  }

  /// 查找session会话信息
  Future<SessionInfo> queryUserSession(int userId, int serverId) async {
    var sql = 'select id,userId,serverId,lastUsed,loginResponse from session_info ';
    sql += 'where userId=? and serverId=?';
    var maps = await db.rawQuery(sql, [userId, serverId]);

    var rs = List.generate(maps.length, (i) {
      return SessionInfo(
        id: maps[i]['id'],
        userId: maps[i]['userId'],
        serverId: maps[i]['serverId'],
        lastUsed: maps[i]['lastUsed'],
        loginResponse: maps[i]['loginResponse'],
      );
    });

    if(rs.isEmpty) {
      return null;
    }

    return rs[0];
  }

  /// 删除指定的会话信息
  Future<void> delSessionToken(int sessionId) async {
    var sql = "update session_info set loginResponse=? where id=?";
    await db.rawUpdate(sql, ['', sessionId]);
  }

  /// 登入的话，应更新会话信息，若没有 则应新增
  Future<int> saveSession(int serverId, SessionInfo f) async {
    var sessionId = 0;

    var existSession = await queryUserSession(f.userId, serverId);
    if(existSession == null) {
      // insert
      sessionId = await addSessionInfo(f);
    } else {
      // update lastUsed and response
      sessionId = existSession.id;
      var changed = await saveSessionInfo(sessionId, f);
      if(changed == 0) {
        print('逻辑错误，未正常更新');
      }
      if(changed > 1) {
        print('逻辑错误，更新记录不止一条');
      }
      if(changed == 1) {
        // print('正常更新');
      }
    }

    // 最后应返回 sessionId
    return sessionId;
  }
}
